<?php
class MessageThread extends Zend_Db_Table_Abstract {
	
	const THREAD_ID = "thread_id";
	const SUBJECT = "subject";
	const USER_MESSAGE_ID = "user_message_id";
	
	protected $_primary = "id";
	protected $_name = "message_thread";
	
	protected $_referenceMap = array(
        'UserMessage' => array(
            'columns' => 'user_message_id',
            'refTableClass' => 'UserMessage',
            'refColumns' => 'id'    
        )
    );
    
    /**
     * Add to thread, thread id is required.
     * 
     * @param $data
     * @return return id
     */
    public function addToThread($data) {
    	return $this->insert($data);
    }
    
    /**
     * Find by thread id.
     * 
     * @param $threadId
     * @return thread object
     */
    public function findByThreadId($threadId) {
    	$select = $this->select()->where("thread_id=?", $threadId);
    	return $this->fetchRow($select);
    }
    
    /**
     * Get thread subject.
     * 
     * @param $threadId
     */
    public function getSubject($threadId) {
    	$select = $this->select()
    	->where("thread_id=?", $threadId)
    	->where("subject is not null");
    	$row = $this->fetchRow($select);
    	return $row->subject;
    }
    
    /**
     * Find user messages by thread id.
     *  
     * @param $threadId
     * @return user messages, order by dtmcreated asc
     */
    public static function findByThread($threadId) {
    	$table = new MessageThread();
    	$select = $table->select(Zend_Db_Table::SELECT_WITH_FROM_PART)
        ->setIntegrityCheck(false)
        ->from(array("t"=>"message_thread"), array("user_message_id"=>"t.user_message_id"))
        ->join(array("um"=>"user_message"), "um.id=t.user_message_id", array("status"=>"um.status", "target_user_id"=>"um.target_user_id"))
        ->join(array("m"=>"message"), "m.id=um.message_id", array("message_id"=>"m.id", "message"=>"m.message", "creator"=>"m.user_id", "created"=>"m.created"))
        ->where("t.thread_id=?", $threadId)
        ->order("m.created ASC");
        
        return $table->fetchAll($select);
    }
    
    /**
     * Find all message thread that send to given user.
     * 
     * @param $user
     * @return message thead, order by created date DESC
     */
    public static function findAllMessageThreadSendToUser($user) {
    	// sql query
    	//select t.thread_id, m.created from message_thread as t join user_message as um on t.user_message_id=um.id join message as m on m.id=um.message_id where um.target_user_id=1  group by t.thread_id order by m.created desc;
    	$table = new MessageThread();
    	$select = $table->select(Zend_Db_Table::SELECT_WITH_FROM_PART)
        ->setIntegrityCheck(false)
        ->from(array("t"=>"message_thread"), array("thread_id"=>"t.thread_id", "subject"=>"t.subject", "created"=>"m.created"))
        ->join(array("um"=>"user_message"), "um.id=t.user_message_id")
        ->join(array("m"=>"message"), "m.id=um.message_id")
        ->where("um.target_user_id=?", $user->id)
        ->group("t.thread_id")
        ->group("t.subject")
        ->order("m.created DESC");
    	return $table->fetchAll($select);
    }
    
    public static function findAllMessageThreadSendByUser($user) {
    	$table = new MessageThread();
        $select = $table->select(Zend_Db_Table::SELECT_WITH_FROM_PART)
        ->setIntegrityCheck(false)
        ->from(array("t"=>"message_thread"), array("thread_id"=>"t.thread_id", "subject"=>"t.subject", "created"=>"m.created"))
        ->join(array("um"=>"user_message"), "um.id=t.user_message_id")
        ->join(array("m"=>"message"), "m.id=um.message_id")
        ->where("m.user_id=?", $user->id)
        ->group("t.thread_id")
        ->group("t.subject")
        ->order("m.created DESC");
        return $table->fetchAll($select);
    }
    
    /**
     * Get last reply user info in given thread, excluded given user, if any
     * 
     * @param $excludedUser exclude given user
     * @param $threadId
     * @return last reply user info, include, user id, created date of message
     */
    public static function findLastReplyInfoInThread($excludedUser=null, $threadId) {
    	$table = new MessageThread();
    	$select = $table->select(Zend_Db_Table::SELECT_WITH_FROM_PART)
        ->setIntegrityCheck(false)
        ->from(array("t"=>"message_thread"), array("uid"=>"m.user_id", "created"=>"m.created", "message"=>"m.message"))
        ->join(array("um"=>"user_message"), "um.id=t.user_message_id")
        ->join(array("m"=>"message"), "m.id=um.message_id")
        ->where("t.thread_id=?", $threadId);
        if (!empty($excludedUser)) {
        	$select = $select->where("m.user_id!=?", $excludedUser->id);
        }
        $select = $select->order("m.created DESC")
        ->limit(1);
        $row = $table->fetchRow($select);
        
        return $row;
    }
    
    /**
     * Get last sent user info in given thread, excluded given user, if any
     * 
     * @param $excludedUser exclude given user
     * @param $threadId
     * @return last reply user info, include, user id, created date of message
     */
    public static function findLastSentInfoInThread($excludedUser=null, $threadId) {
        $table = new MessageThread();
        $select = $table->select(Zend_Db_Table::SELECT_WITH_FROM_PART)
        ->setIntegrityCheck(false)
        ->from(array("t"=>"message_thread"), array("created"=>"m.created", "message"=>"m.message"))
        ->join(array("um"=>"user_message"), "um.id=t.user_message_id", array("uid"=>"um.target_user_id"))
        ->join(array("m"=>"message"), "m.id=um.message_id")
        ->where("t.thread_id=?", $threadId);
        if (!empty($excludedUser)) {
            $select = $select->where("um.target_user_id!=?", $excludedUser->id);
        }
        $select = $select->order("m.created DESC")
        ->limit(1);
        $row = $table->fetchRow($select);
        
        return $row;
    }
    
    /**
     * Return if given thread has all read message.
     * 
     * @param $user
     * @param $threadId
     * @return return true if message thread has all read message
     */
    public static function isReadMessageThread($user, $threadId) {
    	$table = new MessageThread();
        $select = $table->select(Zend_Db_Table::SELECT_WITH_FROM_PART)
        ->setIntegrityCheck(false)
        ->from(array("t"=>"message_thread"), array("num_of_unread"=>"count(*)"))
        ->join(array("um"=>"user_message"), "um.id=t.user_message_id")
        ->join(array("m"=>"message"), "m.id=um.message_id")
        ->where("t.thread_id=?", $threadId)
        ->where("m.user_id!=?", $user->id)
        ->where("um.status=?", UserMessageStatus::UNREAD);
        $row = $table->fetchRow($select);
        return !$row["num_of_unread"] > 0;
    }
    
    /**
     * Get all user messages for given thread.
     * 
     * @param $thread
     * @return array of user messages
     */
    public static function getUserMessages($thread) {
    	return $thread->findParentRow("UserMessage");
    }
}

?>